package com.ims.repository;



import com.b2c.common.third.thymeleaf.PagingResponse;
import com.b2c.common.utils.MD5Utils;
import com.b2c.common.utils.RandomUtils;
import com.b2c.common.utils.ResultVo;
import com.ims.entity.ManageUserEntity;
import com.ims.entity.vo.CommonAttributeVo;
import com.ims.entity.vo.UserMenuVo;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.StringUtils;

import java.sql.*;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

/**
 * 描述：
 * 管理员Repository
 *
 * @author qlp
 * @date 2018-12-26 5:05 PM
 */
@Repository
public class ManageUserRepository {
    @Autowired
    private JdbcTemplate jdbcTemplate;

    /**
     * 根据用户名查询用户
     *
     * @param userName
     * @return
     */
    public ManageUserEntity getUserByUserName(String userName) {
        String sql = "SELECT * FROM manage_user WHERE user_name=? or mobile= ? LIMIT 1";
        List<ManageUserEntity> users = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(ManageUserEntity.class), userName, userName);
        if (users == null || users.size() == 0) return null;
        else return users.get(0);
    }

    /**
     * 根据货id查詢用戶姓名
     *
     * @return
     */
    public String getNameById(Integer id) {
        String userName;
        try {
            userName = jdbcTemplate.queryForObject("SELECT true_name FROM manage_user WHERE id = ? ", String.class, id);
        } catch (EmptyResultDataAccessException e) {
            userName = "";
        }
        return userName;
    }

    /**
     * 根据用户Id查询管理员信息根据用户Id查询管理员信息
    /*
     * 根据用户Id查询管理员信息
     * @param userId
     * @return
     */
    public ManageUserEntity getManageUserById(Integer userId){
        try {
            return jdbcTemplate.queryForObject("SELECT * FROM manage_user WHERE id=?",new BeanPropertyRowMapper<>(ManageUserEntity.class),userId);
        }catch (Exception e){
            return null;
        }

    }

    /**
     * 修改用户登录密码
     *
     * @param newPwd
     * @return
     */
    public void updManageUserPwd(int userId, String newPwd, String pwd_rand) {
        jdbcTemplate.update("UPDATE manage_user SET user_pwd = ?,user_salf=? WHERE id = ? ", newPwd, pwd_rand, userId);
    }

    /**
     * 登入账号列表
     */
    @Transactional
    public PagingResponse<ManageUserEntity> getUserList(Integer pageIndex, Integer pageSize, String name, String mobile) {
        StringBuilder sb = new StringBuilder("SELECT SQL_CALC_FOUND_ROWS * from manage_user  where isDelete=0 ");
        List<Object> params = new ArrayList<>();
        if (!StringUtils.isEmpty(name)) {
            sb.append(" and true_name = ? ");
            params.add(name);
        }
        if (!StringUtils.isEmpty(mobile)) {
            sb.append(" and mobile = ? ");
            params.add(mobile);
        }
        sb.append(" ORDER BY id ASC LIMIT ?,?");
        params.add((pageIndex - 1) * pageSize);
        params.add(pageSize);

        List<ManageUserEntity> lists = jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<>(ManageUserEntity.class), params.toArray(new Object[params.size()]));

        return new PagingResponse<>(pageIndex, pageSize, getTotalSize(), lists);
    }

    protected int getTotalSize() {
        return jdbcTemplate.queryForObject("SELECT FOUND_ROWS() as row_num;", int.class);
    }

    /**
     * 根据Id删除账号
     **/
    @Transactional
    public Integer delUser(Integer id) {
        return jdbcTemplate.update("update  manage_user set isDelete=1 where id=? ", id);
    }
    /**
     * 检查用户是否存在
     *
     * @param mobile
     * @return
     */
    public int checkManageUserByMobile(Integer userId, String mobile) {
        if (userId > 0)
            return jdbcTemplate.queryForObject("SELECT IFNULL(COUNT(id),0)  from manage_user where (id>? OR id<?) and mobile=?  and isDelete=0", Integer.class, userId, userId, mobile);
        return jdbcTemplate.queryForObject("SELECT IFNULL(COUNT(id),0)  from manage_user where mobile=?  and isDelete=0", Integer.class, mobile);
    }

    public Integer checkUserName(String user){
        try {
            return jdbcTemplate.update("select id from manage_user where user_name=? and isDelete=0",user);
        }catch (Exception e){
            return 0;
        }
    }

    public void updAddUser(Integer userId, String name, String mobile, String user, String pwd, String state,String groupName) {
        String oldpwd = "";

        if (userId > 0)
            oldpwd = jdbcTemplate.queryForObject("SELECT user_pwd from manage_user where id=?", String.class, userId);

        String pwd_rand = RandomUtils.randomString(8);
        String password = !StringUtils.isEmpty(pwd) ? MD5Utils.MD5Encode(MD5Utils.MD5Encode(pwd) + pwd_rand) : oldpwd;

        if (userId > 0 ) {
            if(StringUtils.isEmpty(pwd)){
                //不修改密码
                jdbcTemplate.update("UPDATE manage_user SET true_name=?,mobile=?,status=?,groupName=? WHERE id=?", user, mobile, state,groupName,userId);
            }else {
                //修改密码
                jdbcTemplate.update("UPDATE manage_user SET true_name=?,user_pwd=?,user_salf=?,mobile=?,status=?,groupName=? WHERE id=?", user, password, pwd_rand, mobile, state,groupName,userId);
            }
        } else {
            KeyHolder keyHolder = new GeneratedKeyHolder();
            jdbcTemplate.update(new PreparedStatementCreator() {
                @Override
                public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                    PreparedStatement ps = connection.prepareStatement("insert into manage_user set user_name=?,true_name =?,user_pwd=?,user_salf=?,mobile = ?,status=?,groupName=?,add_time=unix_timestamp(now())", Statement.RETURN_GENERATED_KEYS);
                    ps.setString(1, name);
                    ps.setString(2, user);
                    ps.setString(3, password);
                    ps.setString(4, pwd_rand);
                    ps.setString(5, mobile);
                    ps.setString(6, state);
                    ps.setString(7,groupName);
                    return ps;
                }
            }, keyHolder);
            initUserPermissionMenu(keyHolder.getKey().intValue());
        }
    }
    /**
     * 查询用户已授权菜单访问地址,已授权返回true
     * @param userId
     * @return
     */
    public boolean checkUserAuth(Integer userId,String requestUrl){
        /**1.超级管理员全部授权**/
        if(userId==1)return true;

        /**2.数据库未添加菜单默认已授权**/
        var urlList=jdbcTemplate.query("select trim(url) url from system_menu ", new ResultSetExtractor<List<String>>() {
            @Override
            public List<String> extractData(ResultSet resultSet) throws SQLException, DataAccessException {
                List<String> urlList= new ArrayList<>();
                while (resultSet.next()){
                    urlList.add(resultSet.getString("url"));
                }
                return urlList;
            }
        });
        if(urlList.contains(requestUrl)==false)return true;

        /**3.检查访问url是否已授权**/
        var list=jdbcTemplate.query("select trim(menu.url) url from user_permission perm LEFT JOIN system_menu menu ON perm.menu_id=menu.id where perm.user_id=? and menu.status=0 and perm.auth=0", new ResultSetExtractor<List<String>>() {
            @Override
            public List<String> extractData(ResultSet res) throws SQLException, DataAccessException {
                List<String> permUrl= new ArrayList<>();
                while (res.next()){
                    permUrl.add(res.getString("url"));
                }
                return permUrl;
            }
        },userId);
        return list.contains(requestUrl.strip());
    }
    /**
     * 初始化用户权限菜单
     * @param userId
     */
    public void initUserPermissionMenu(Integer userId){
        var list = jdbcTemplate.query("select id  from system_menu ",new BeanPropertyRowMapper<>(CommonAttributeVo.class));
        list.forEach(menu->{
            Integer menuCount= jdbcTemplate.queryForObject("select count(0) from user_permission where user_id=? and menu_id=?",Integer.class,userId,menu.getId());
            if(menuCount.intValue()==0)jdbcTemplate.update("insert into user_permission set auth=?,user_id=?,menu_id=?", userId==1 ? 0 : 1,userId,menu.getId());
        });
    }
    /**
     * 查询用户权限菜单列表
     * @param userId
     * @return
     */
    public List<UserMenuVo> getUserPerMissionMenu(Integer userId){
        StringBuilder sb = new StringBuilder();
        sb.append("select menu.*,perm.auth  from system_menu menu");
        sb.append(" LEFT JOIN user_permission perm ON menu.id=perm.menu_id");
        sb.append(" where menu.status=0 and perm.user_id=? and menu.parent_id=?  ");
        var list = jdbcTemplate.query(sb.toString(),new BeanPropertyRowMapper<>(UserMenuVo.class),userId,0);
        for(var item:list){
            item.setChildren(jdbcTemplate.query(sb.toString(),new BeanPropertyRowMapper<>(UserMenuVo.class),userId,item.getId()));
        }
        return list;
    }
    /**
     * 设置用户菜单权限
     * @param userId
     * @param menuIds
     */
    public void setUserMenu(Integer userId,String[] menuIds){
        /**1.未选择任何菜单全部禁用**/
        if(StringUtils.isEmpty(menuIds)){
            jdbcTemplate.update("update user_permission SET status=1 WHERE user_id=?  ",userId);
            return;
        }
        List<String> newMenus=Arrays.asList(menuIds);

        /**2.取消菜单权限**/
        var list = jdbcTemplate.query("select menu_id as id from user_permission where user_id=?",new BeanPropertyRowMapper<>(CommonAttributeVo.class),userId);

        list.forEach(oldMenu->{
            if(newMenus.contains(oldMenu.getId())==false){
                jdbcTemplate.update("UPDATE user_permission perm LEFT JOIN system_menu menu ON perm.menu_id=menu.id SET perm.auth=1 WHERE menu.parent_id>0 and perm.user_id=? and perm.menu_id=? ",userId,oldMenu.getId());
            }
        });
        list.forEach(oldMenu->{
            if(newMenus.contains(oldMenu.getId())==false){
                jdbcTemplate.update("UPDATE user_permission perm LEFT JOIN system_menu menu ON perm.menu_id=menu.id SET perm.auth=0 WHERE menu.parent_id=0 and perm.user_id=? and perm.menu_id=? ",userId,oldMenu.getId());
            }
        });
        /**3.设置用户最新菜单权限**/
        newMenus.forEach(menus->{
            jdbcTemplate.update("update user_permission SET auth=0 WHERE user_id=? and menu_id=?",userId,Integer.parseInt(menus));
        });
        /***4.把新菜单上级权限改回来*****/
        for (var mId:menuIds) {
            try {
                var menu = jdbcTemplate.queryForObject("SELECT * FROM system_menu where id=?", new BeanPropertyRowMapper<>(UserMenuVo.class),mId);
                jdbcTemplate.update("update user_permission SET auth=0 WHERE menu_id=? and user_id=?", menu.getParentId(),userId);
            }catch (Exception e){
                throw e;
            }
        }
    }

}
